import requests
from lxml import etree
import csv
import pymysql
def get_html(url):
    headers={"User-Agent":"Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/122.0.0.0 Safari/537.36"}
    try:
        r=requests.get(url=url,headers=headers)
        r.encoding=r.apparent_encoding
        r.raise_for_status()
        return r.text
    except Exception as e:
        print(e)
def parser(html):
    doc=etree.HTML(html)
    out_list=[]
    for row in doc.xpath('//*[@id="content"]//li'):
        title=row.xpath('.//h2/a/text()')[0].strip()
        score=row.xpath('.//p[@class="rating"]/span[2]/text()')[0].strip()
        info=row.xpath('.//p[@class="color-gray"]/text()')[0].strip().split("/")
        describe=row.xpath('.//p[3]/text()')[0].strip()
        row_list=[
            title,
            score,
            info[0],
            info[-2],
            info[-1],
            describe
        ]
        out_list.append(row_list)
    return out_list;
def save_mysql(sql,val,**dbinfo):
    try:
        connect=pymysql.connect(**dbinfo)
        cursor=connect.cursor()
        cursor.executemany(sql,val)
        connect.commit()
    except Exception as e:
        connect.rollback()
        print(e)
    finally:
        cursor.close()
        connect.close()
if __name__ == '__main__':
    url="http://www.bspider.top/doubanbook/"
    html=get_html(url)
    out_list=parser(html)
    parms={
        "host":"127.0.0.1",
        "user":"root",
        "password":"root",
        "db":"spider",
        "charset":"utf8",
        "cursorclass":"pymysql.cursors.DictCursor"
    }
    sql="insert into bookinfo(bookname,score,autor,press,pubdate,describ)"\
        "values(%s,%s,%s,%s,%s,%s)"
    save_mysql(sql,out_list,**parms)